﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Globalization;
using System.IO;
using System.IO.Packaging;
using System.Security.Cryptography;
using System.Text;
using System.Xml;

namespace Kivensoft.XlsWriter {
	/// <summary>
	/// XML文件生成，压缩
	/// </summary>
	/// <remarks>内部使用类</remarks>
	class LowLevel {
		private static DocumentPath WORKBOOK = new DocumentPath("workbook.xml", "xl/");
		private static DocumentPath STYLES = new DocumentPath("styles.xml", "xl/");
		private static DocumentPath APP_PROPERTIES = new DocumentPath("app.xml", "docProps/");
		private static DocumentPath CORE_PROPERTIES = new DocumentPath("core.xml", "docProps/");
		private static DocumentPath SHARED_STRINGS = new DocumentPath("sharedStrings.xml", "xl/");
		private static RNGCryptoServiceProvider RNGcsp = new RNGCryptoServiceProvider();

		private CultureInfo culture;
		private Workbook workbook;
		//private Dictionary<string, string> sharedStrings;
		private SortedMap sharedStrings;
		private int sharedStringsTotalCount;

		public LowLevel(Workbook workbook) {
			culture = CultureInfo.CreateSpecificCulture("zh-CN");
			this.workbook = workbook;
			//sharedStrings = new Dictionary<string, string>();
			sharedStrings = new SortedMap();
			sharedStringsTotalCount = 0;
		}

		public void Save() {
			using (Package p = Package.Open(workbook.FileName, FileMode.Create)) {
				Save(p);
			}
		}

		public void Save(Stream output) {
			using (Package p = Package.Open(output, FileMode.Create)) {
				Save(p);
			}
		}

		private void Save(Package p) {
			workbook.ResolveMergedCells();
			XmlDocument workbookDocument = CreateWorkbookDocument();
			XmlDocument stylesheetDocument = CreateStyleSheetDocument();
			XmlDocument worksheetDocument;
			DocumentPath sheetPath;
			List<Uri> sheetURIs = new List<Uri>();

			try {
				Uri workbookUri = new Uri(WORKBOOK.GetFullPath(), UriKind.Relative);
				Uri stylesheetUri = new Uri(STYLES.GetFullPath(), UriKind.Relative);
				Uri appPropertiesUri = new Uri(APP_PROPERTIES.GetFullPath(), UriKind.Relative);
				Uri corePropertiesUri = new Uri(CORE_PROPERTIES.GetFullPath(), UriKind.Relative);
				Uri sharedStringsUri = new Uri(SHARED_STRINGS.GetFullPath(), UriKind.Relative);

				PackagePart pp = p.CreatePart(workbookUri, @"application/vnd.openxmlformats-officedocument.spreadsheetml.sheet.main+xml", CompressionOption.Normal);
				p.CreateRelationship(pp.Uri, TargetMode.Internal, @"http://schemas.openxmlformats.org/officeDocument/2006/relationships/officeDocument", "rId1");
				p.CreateRelationship(corePropertiesUri, TargetMode.Internal, @"http://schemas.openxmlformats.org/package/2006/relationships/metadata/core-properties", "rId2"); //!
				p.CreateRelationship(appPropertiesUri, TargetMode.Internal, @"http://schemas.openxmlformats.org/officeDocument/2006/relationships/extended-properties", "rId3"); //!

				using (MemoryStream ms = new MemoryStream()) { // Write workbook.xml
					WriteXMLStream(ms, workbookDocument);
					LowLevel.CopyStream(ms, pp.GetStream());
				}
				int idCounter = workbook.Worksheets.Count + 1;

				pp.CreateRelationship(stylesheetUri, TargetMode.Internal, @"http://schemas.openxmlformats.org/officeDocument/2006/relationships/styles", "rId" + idCounter.ToString());
				pp.CreateRelationship(sharedStringsUri, TargetMode.Internal, @"http://schemas.openxmlformats.org/officeDocument/2006/relationships/sharedStrings", "rId" + (idCounter + 1).ToString());

				foreach (Worksheet item in workbook.Worksheets) {
					sheetPath = new DocumentPath("sheet" + item.SheetID.ToString() + ".xml", "xl/worksheets");
					sheetURIs.Add(new Uri(sheetPath.GetFullPath(), UriKind.Relative));
					pp.CreateRelationship(sheetURIs[sheetURIs.Count - 1], TargetMode.Internal, @"http://schemas.openxmlformats.org/officeDocument/2006/relationships/worksheet", "rId" + item.SheetID.ToString());
				}
				int i = 0;
				foreach (Worksheet item in workbook.Worksheets) {
					pp = p.CreatePart(sheetURIs[i], @"application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml", CompressionOption.Normal);
					i++;
					worksheetDocument = CreateWorksheetPart(item);
					using (MemoryStream ms = new MemoryStream()) {
						WriteXMLStream(ms, worksheetDocument);
						LowLevel.CopyStream(ms, pp.GetStream());
					}
				}

				pp = p.CreatePart(sharedStringsUri, @"application/vnd.openxmlformats-officedocument.spreadsheetml.sharedStrings+xml", CompressionOption.Normal);
				using (MemoryStream ms = new MemoryStream()) {
					WriteXMLStream(ms, CreateSharedStringsDocument());
					LowLevel.CopyStream(ms, pp.GetStream());
				}

				pp = p.CreatePart(stylesheetUri, @"application/vnd.openxmlformats-officedocument.spreadsheetml.styles+xml", CompressionOption.Normal);
				using (MemoryStream ms = new MemoryStream()) {
					WriteXMLStream(ms, stylesheetDocument);
					LowLevel.CopyStream(ms, pp.GetStream());
				}

				if (workbook.WorkbookMetadata != null) {
					pp = p.CreatePart(appPropertiesUri, @"application/vnd.openxmlformats-officedocument.extended-properties+xml", CompressionOption.Normal);
					using (MemoryStream ms = new MemoryStream()) {
						WriteXMLStream(ms, CreateAppPropertiesDocument());
						LowLevel.CopyStream(ms, pp.GetStream());
					}
					pp = p.CreatePart(corePropertiesUri, @"application/vnd.openxmlformats-package.core-properties+xml", CompressionOption.Normal);
					using (MemoryStream ms = new MemoryStream()) {
						WriteXMLStream(ms, CreateCorePropertiesDocument());
						LowLevel.CopyStream(ms, pp.GetStream());
					}
				}
			}
			catch (Exception e) {
				throw new IOException("An error occurred while saving. See inner exception for details.", e);
			}
		}

		/// <summary>
		/// 创建工作表
		/// </summary>
		private XmlDocument CreateWorksheetPart(Worksheet worksheet) {
			worksheet.RecalculateAutoFilter();
			worksheet.RecalculateColumns();
			XmlDocument worksheetDocument = new XmlDocument();
			List<List<Cell>> celldata = GetSortedSheetData(worksheet);
			StringBuilder sb = new StringBuilder();
			string line;
			sb.Append("<worksheet xmlns=\"http://schemas.openxmlformats.org/spreadsheetml/2006/main\" xmlns:mc=\"http://schemas.openxmlformats.org/markup-compatibility/2006\" mc:Ignorable=\"x14ac\" xmlns:x14ac=\"http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac\">");

			if (worksheet.SelectedCells != null) {
				sb.Append("<sheetViews><sheetView workbookViewId=\"0\"");
				if (workbook.SelectedWorksheet == worksheet.SheetID - 1)
					sb.Append(" tabSelected=\"1\"");
				sb.Append("><selection sqref=\"");
				sb.Append(worksheet.SelectedCells.ToString());
				sb.Append("\" activeCell=\"");
				sb.Append(worksheet.SelectedCells.Value.StartAddress.ToString());
				sb.Append("\"/></sheetView></sheetViews>");
			}

			sb.Append("<sheetFormatPr x14ac:dyDescent=\"0.25\" defaultRowHeight=\"" + worksheet.DefaultRowHeight.ToString("G", culture) + "\" baseColWidth=\"" + worksheet.DefaultColumnWidth.ToString("G", culture) + "\"/>");

			string colWidths = CreateColsString(worksheet);
			if (string.IsNullOrEmpty(colWidths) == false) {
				sb.Append("<cols>");
				sb.Append(colWidths);
				sb.Append("</cols>");
			}
			sb.Append("<sheetData>");
			foreach (List<Cell> item in celldata) {
				line = CreateRowString(item, worksheet);
				sb.Append(line);
			}
			sb.Append("</sheetData>");

			sb.Append(CreateMergedCellsString(worksheet));
			sb.Append(CreateSheetProtectionString(worksheet));

			if (worksheet.AutoFilterRange != null) {
				sb.Append("<autoFilter ref=\"" + worksheet.AutoFilterRange.Value.ToString() + "\"/>");
			}

			sb.Append("</worksheet>");
			worksheetDocument.LoadXml(sb.ToString());
			XmlDeclaration dec = worksheetDocument.CreateXmlDeclaration("1.0", "UTF-8", "yes");
			XmlElement root = worksheetDocument.DocumentElement;
			worksheetDocument.InsertBefore(dec, root);
			return worksheetDocument;
		}

		/// <summary>
		/// 创建样式表
		/// </summary>
		private XmlDocument CreateStyleSheetDocument() {
			List<Style.Border> borders;
			List<Style.Fill> fills;
			List<Style.Font> fonts;
			List<Style.NumberFormat> numberFormats;
			List<Style.CellXf> cellXfs; // Not used at the moment
			int numFormatCount = 0;
			workbook.ReorganizeStyles(out borders, out fills, out fonts, out numberFormats, out cellXfs);
			string bordersString = CreateStyleBorderString(borders);
			string fillsString = CreateStyleFillString(fills);
			string fontsString = CreateStyleFontString(fonts);
			string numberFormatsString = CreateStyleNumberFormatString(numberFormats, out numFormatCount);
			string xfsStings = CreateStyleXfsString(workbook.Styles);
			string mruColorString = CreateMruColorsString(fonts, fills);
			string colorsString = CreateColorsString();
			StringBuilder sb = new StringBuilder();
			sb.Append("<styleSheet xmlns=\"http://schemas.openxmlformats.org/spreadsheetml/2006/main\" xmlns:mc=\"http://schemas.openxmlformats.org/markup-compatibility/2006\" mc:Ignorable=\"x14ac\" xmlns:x14ac=\"http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac\">");
			if (numFormatCount > 0) {
				sb.Append("<numFmts count=\"" + numFormatCount.ToString("G", culture) + "\">");
				sb.Append(numberFormatsString + "</numFmts>");
			}
			sb.Append("<fonts x14ac:knownFonts=\"1\" count=\"" + fonts.Count.ToString("G", culture) + "\">");
			sb.Append(fontsString + "</fonts>");
			sb.Append("<fills count=\"" + fills.Count.ToString("G", culture) + "\">");
			sb.Append(fillsString + "</fills>");
			sb.Append("<borders count=\"" + borders.Count.ToString("G", culture) + "\">");
			sb.Append(bordersString + "</borders>");
			sb.Append("<cellXfs count=\"" + workbook.Styles.Count.ToString("G", culture) + "\">");
			sb.Append(xfsStings + "</cellXfs>");
			if (workbook.WorkbookMetadata != null) {
				if (string.IsNullOrEmpty(mruColorString) == false && workbook.WorkbookMetadata.UseColorMRU == true) {
					sb.Append("<colors>");
					sb.Append(mruColorString);
					sb.Append("</colors>");
				}
			}
			sb.Append(colorsString);
			sb.Append("</styleSheet>");
			XmlDocument doc = new XmlDocument();
			doc.LoadXml(sb.ToString());
			XmlDeclaration dec = doc.CreateXmlDeclaration("1.0", "UTF-8", "yes");
			XmlElement root = doc.DocumentElement;
			doc.InsertBefore(dec, root);
			return doc;
		}

		/// <summary>
		/// 创建应用属性表
		/// </summary>
		private XmlDocument CreateAppPropertiesDocument() {
			XmlDocument doc = new XmlDocument();
			StringBuilder sb = new StringBuilder();
			sb.Append("<Properties xmlns=\"http://schemas.openxmlformats.org/officeDocument/2006/extended-properties\" xmlns:vt=\"http://schemas.openxmlformats.org/officeDocument/2006/docPropsVTypes\">");
			sb.Append(CreateAppString());
			sb.Append("</Properties>");
			doc.LoadXml(sb.ToString());
			XmlDeclaration dec = doc.CreateXmlDeclaration("1.0", "UTF-8", "yes");
			XmlElement root = doc.DocumentElement;
			doc.InsertBefore(dec, root);
			return doc;
		}

		/// <summary>
		/// 创建核心属性表
		/// </summary>
		private XmlDocument CreateCorePropertiesDocument() {
			XmlDocument doc = new XmlDocument();
			StringBuilder sb = new StringBuilder();
			sb.Append("<cp:coreProperties xmlns:cp=\"http://schemas.openxmlformats.org/package/2006/metadata/core-properties\" xmlns:dc=\"http://purl.org/dc/elements/1.1/\" xmlns:dcterms=\"http://purl.org/dc/terms/\" xmlns:dcmitype=\"http://purl.org/dc/dcmitype/\" xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\">");
			sb.Append(CreateCorePropertiesString());
			sb.Append("</cp:coreProperties>");
			doc.LoadXml(sb.ToString());
			XmlDeclaration dec = doc.CreateXmlDeclaration("1.0", "UTF-8", "yes");
			XmlElement root = doc.DocumentElement;
			doc.InsertBefore(dec, root);
			return doc;
		}

		/// <summary>
		/// 创建工作薄
		/// </summary>
		private XmlDocument CreateWorkbookDocument() {
			if (workbook.Worksheets.Count == 0)
				throw new OutOfRangeException("The workbook can not be created because no worksheet was defined.");
			XmlDocument doc = new XmlDocument();
			StringBuilder sb = new StringBuilder();
			sb.Append("<workbook xmlns=\"http://schemas.openxmlformats.org/spreadsheetml/2006/main\"")
				.Append(" xmlns:r=\"http://schemas.openxmlformats.org/officeDocument/2006/relationships\">");
			if (workbook.SelectedWorksheet > 0)
				sb.Append("<bookViews><workbookView activeTab=\"")
					.Append(workbook.SelectedWorksheet.ToString("G", culture))
					.Append("\"/></bookViews>");
			if (workbook.UseWorkbookProtection == true) {
				sb.Append("<workbookProtection");
				if (workbook.LockWindowsIfProtected == true)
					sb.Append(" lockWindows=\"1\"");
				if (workbook.LockStructureIfProtected == true)
					sb.Append(" lockStructure=\"1\"");
				if (string.IsNullOrEmpty(workbook.WorkbookProtectionPassword) == false)
					sb.Append("workbookPassword=\"")
						.Append(GeneratePasswordHash(workbook.WorkbookProtectionPassword))
						.Append("\"");
				sb.Append("/>");
			}
			sb.Append("<sheets>");
			foreach (Worksheet item in workbook.Worksheets) {
				sb.Append("<sheet r:id=\"rId").Append(item.SheetID.ToString())
					.Append("\" sheetId=\"").Append(item.SheetID.ToString())
					.Append("\" name=\"").Append(LowLevel.EscapeXMLAttributeChars(item.SheetName))
					.Append("\"/>");
			}
			sb.Append("</sheets></workbook>");
			doc.LoadXml(sb.ToString());
			XmlDeclaration dec = doc.CreateXmlDeclaration("1.0", "UTF-8", "yes");
			XmlElement root = doc.DocumentElement;
			doc.InsertBefore(dec, root);
			//workbookDocument = doc;
			return doc;
		}

		/// <summary>
		/// 兼容保留函数，仅用于当创建样式表错误时应急调试
		/// </summary>
		[Obsolete("兼容保留函数，正常版本请勿调用")]
		private XmlDocument CreateStyleSheetDocumentFallback() {
			XmlDocument doc = new XmlDocument();
			StringBuilder sb = new StringBuilder();
			sb.Append("<styleSheet xmlns=\"http://schemas.openxmlformats.org/spreadsheetml/2006/main\"")
				.Append(" xmlns:mc=\"http://schemas.openxmlformats.org/markup-compatibility/2006\"")
				.Append(" mc:Ignorable=\"x14ac\"")
				.Append(" xmlns:x14ac=\"http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac\">")
				.Append("<fonts x14ac:knownFonts=\"1\" count=\"4\">")
				.Append("<font><sz val=\"11\" /><name val=\"Calibri\" /><family val=\"2\" /><scheme val=\"minor\" /></font>")            // Date
				.Append("<font><b/><sz val=\"11\" /><name val=\"Calibri\" /><family val=\"2\" /><scheme val=\"minor\" /></font>")    // Bold
				.Append("<font><i/><sz val=\"11\" /><name val=\"Calibri\" /><family val=\"2\" /><scheme val=\"minor\" /></font>")    // Italic
				.Append("<font><u/><sz val=\"11\" /><name val=\"Calibri\" /><family val=\"2\" /><scheme val=\"minor\" /></font>")    // Underline
				.Append("</fonts>");

			sb.Append("<fills count=\"1\">")
				.Append("<fill><patternFill patternType=\"none\" /></fill>")
				.Append("</fills>");

			sb.Append("<borders count=\"1\">")
				.Append("<border><left /><right /><top /><bottom /><diagonal /></border>")
				.Append("</borders>");

			sb.Append("<cellXfs count=\"3\">")
				.Append("<xf borderId=\"0\" fillId=\"0\" fontId=\"0\" numFmtId=\"0\" xfId=\"0\"/>")
				.Append("<xf borderId=\"0\" fillId=\"0\" fontId=\"0\" numFmtId=\"14\" applyNumberFormat=\"1\" xfId=\"0\"/>") // DateFormat (s="1")
				.Append("<xf borderId=\"0\" fillId=\"0\" fontId=\"1\" numFmtId=\"0\" applyNumberFormat=\"1\" xfId=\"0\"/>") // Bold  (s="2")
				.Append("<xf borderId=\"0\" fillId=\"0\" fontId=\"2\" numFmtId=\"0\" applyNumberFormat=\"1\" xfId=\"0\"/>") // Italic  (s="3")
				.Append("<xf borderId=\"0\" fillId=\"0\" fontId=\"3\" numFmtId=\"0\" applyNumberFormat=\"1\" xfId=\"0\"/>") // Underline  (s="4")
				.Append("</cellXfs>");

			sb.Append("</styleSheet>");
			doc.LoadXml(sb.ToString());
			XmlDeclaration dec = doc.CreateXmlDeclaration("1.0", "UTF-8", "yes");
			XmlElement root = doc.DocumentElement;
			doc.InsertBefore(dec, root);
			return doc;
		}

		/// <summary>
		/// 创建共享字符串表
		/// </summary>
		private XmlDocument CreateSharedStringsDocument() {
			XmlDocument doc = new XmlDocument();
			StringBuilder sb = new StringBuilder();
			sb.Append("<sst xmlns=\"http://schemas.openxmlformats.org/spreadsheetml/2006/main\" count=\"")
				.Append(sharedStringsTotalCount.ToString("G", culture))
				.Append("\" uniqueCount=\"")
				.Append(sharedStrings.Count.ToString("G", culture))
				.Append("\">");
			// foreach(KeyValuePair<string,string>str in sharedStrings)
			foreach (SortedMap.Tuple str in sharedStrings) {
				sb.Append("<si><t>")
					.Append(EscapeXMLChars(str.Key))
					.Append("</t></si>");
			}
			sb.Append("</sst>");
			doc.LoadXml(sb.ToString());
			XmlDeclaration dec = doc.CreateXmlDeclaration("1.0", "UTF-8", "yes");
			XmlElement root = doc.DocumentElement;
			doc.InsertBefore(dec, root);
			return doc;
		}

		/// <summary>
		/// 解析工作表中的共享字符串
		/// </summary>
		/// <param name="sheet">工作表</param>
		private List<List<Cell>> GetSortedSheetData(Worksheet sheet) {
			List<Cell> temp = new List<Cell>();
			foreach (KeyValuePair<string, Cell> item in sheet.Cells) {
				temp.Add(item.Value);
			}
			temp.Sort();
			List<Cell> line = new List<Cell>();
			List<List<Cell>> output = new List<List<Cell>>();
			if (temp.Count > 0) {
				int rowNumber = temp[0].RowAddress;
				foreach (Cell item in temp) {
					if (item.RowAddress != rowNumber) {
						output.Add(line);
						line = new List<Cell>();
						rowNumber = item.RowAddress;
					}
					line.Add(item);
				}
				if (line.Count > 0) {
					output.Add(line);
				}
			}
			return output;
		}

		/// <summary>
		/// 创建一行数据字符串
		/// </summary>
		/// <param name="columnFields">单元格列表</param>
		/// <param name="worksheet">工作表</param>
		/// <returns>格式化后的字符串</returns>
		private string CreateRowString(List<Cell> columnFields, Worksheet worksheet) {
			int rowNumber = columnFields[0].RowAddress;
			string heigth = "";
			string hidden = "";
			if (worksheet.RowHeights.ContainsKey(rowNumber)) {
				if (worksheet.RowHeights[rowNumber] != worksheet.DefaultRowHeight) {
					heigth = " customHeight=\"1\" ht=\"" + worksheet.RowHeights[rowNumber].ToString("G", culture) + "\"";
					//heigth = " x14ac:dyDescent=\"0.25\" customHeight=\"1\" ht=\"" + worksheet.RowHeights[rowNumber].ToString("G", culture) + "\"";
				}
			}
			if (worksheet.HiddenRows.ContainsKey(rowNumber)) {
				if (worksheet.HiddenRows[rowNumber] == true) {
					hidden = " hidden=\"1\"";
				}
			}
			StringBuilder sb = new StringBuilder();
			if (columnFields.Count > 0)
				sb.Append("<row r=\"").Append((rowNumber + 1).ToString()).Append("\"")
					.Append(heigth + hidden).Append(">");
			else sb.Append("<row").Append(heigth).Append(">");

			string typeAttribute;
			string sValue = "";
			string tValue = "";
			string value = "";

			int col = 0;
			foreach (Cell item in columnFields) {
				tValue = " ";
				if (item.CellStyle != null)
					sValue = " s=\"" + item.CellStyle.InternalID.ToString("G", culture) + "\" ";
				else sValue = "";
				item.ResolveCellType(); // Recalculate the type (for handling DEFAULT)
				if (item.Fieldtype == Cell.CellType.BOOL) {
					typeAttribute = "b";
					tValue = " t=\"" + typeAttribute + "\" ";
					bool bVal = (bool)item.Value;
					value = (bVal == true) ? "1" : "0";
				}
				// Number casting
				else if (item.Fieldtype == Cell.CellType.NUMBER) {
					typeAttribute = "n";
					tValue = " t=\"" + typeAttribute + "\" ";
					Type t = item.Value.GetType();

					if (t == typeof(int))
						value = ((int)item.Value).ToString("G", culture);
					else if (t == typeof(long))
						value = ((long)item.Value).ToString("G", culture);
					else if (t == typeof(double))
						value = ((double)item.Value).ToString("G", culture);
					else if (t == typeof(float))
						value = ((float)item.Value).ToString("G", culture);
				}
				// Date parsing
				else if (item.Fieldtype == Cell.CellType.DATE) {
					typeAttribute = "d";
					DateTime dVal = (DateTime)item.Value;
					value = LowLevel.GetOADateTimeString(dVal);
				}
				// String parsing
				else {
					if (item.Value == null) {
						typeAttribute = "str";
						value = string.Empty;
					}
					else { // Handle sharedStrings
						if (item.Fieldtype == Cell.CellType.FORMULA) {
							typeAttribute = "str";
							value = item.Value.ToString();
						}
						else {
							typeAttribute = "s";
							value = item.Value.ToString();
							if (sharedStrings.ContainsKey(value) == false)
								sharedStrings.Add(value, sharedStrings.Count.ToString("G", culture));
							value = sharedStrings[value];
							sharedStringsTotalCount++;
						}
					}
					tValue = " t=\"" + typeAttribute + "\" ";
				}
				if (item.Fieldtype != Cell.CellType.EMPTY) {
					sb.Append("<c").Append(tValue).Append("r=\"")
						.Append(item.GetCellAddress()).Append("\"")
						.Append(sValue).Append(">");
					if (item.Fieldtype == Cell.CellType.FORMULA) {
						sb.Append("<f>")
							.Append(LowLevel.EscapeXMLChars(item.Value.ToString()))
							.Append("</f>");
					}
					else {
						sb.Append("<v>").Append(LowLevel.EscapeXMLChars(value))
							.Append("</v>");
					}
					sb.Append("</c>");
				}
				else { // Empty cell
					sb.Append("<c").Append(tValue).Append("r=\"")
						.Append(item.GetCellAddress()).Append("\"")
						.Append(sValue).Append("/>");
				}
				col++;
			}
			sb.Append("</row>");
			return sb.ToString();
		}

		/// <summary>
		/// 创建合并单元格字符串
		/// </summary>
		/// <param name="sheet">工作表</param>
		/// <returns>格式化后的合并单元格字符串</returns>
		private string CreateMergedCellsString(Worksheet sheet) {
			if (sheet.MergedCells.Count < 1) return string.Empty;
			
			StringBuilder sb = new StringBuilder();
			sb.Append("<mergeCells count=\"")
				.Append(sheet.MergedCells.Count.ToString("G", culture))
				.Append("\">");
			foreach (KeyValuePair<string, Cell.Range> item in sheet.MergedCells)
				sb.Append("<mergeCell ref=\"").Append(item.Value.ToString()).Append("\"/>");
			
			sb.Append("</mergeCells>");
			return sb.ToString();
		}

		private string CreateSheetProtectionString(Worksheet sheet) {
			if (sheet.UseSheetProtection == false) return string.Empty;

			Dictionary<Worksheet.SheetProtectionValue, int> actualLockingValues = new Dictionary<Worksheet.SheetProtectionValue, int>();
			if (sheet.SheetProtectionValues.Count == 0) {
				actualLockingValues.Add(Worksheet.SheetProtectionValue.selectLockedCells, 1);
				actualLockingValues.Add(Worksheet.SheetProtectionValue.selectUnlockedCells, 1);
			}
			if (sheet.SheetProtectionValues.Contains(Worksheet.SheetProtectionValue.objects) == false) {
				actualLockingValues.Add(Worksheet.SheetProtectionValue.objects, 1);
			}
			if (sheet.SheetProtectionValues.Contains(Worksheet.SheetProtectionValue.scenarios) == false) {
				actualLockingValues.Add(Worksheet.SheetProtectionValue.scenarios, 1);
			}
			if (sheet.SheetProtectionValues.Contains(Worksheet.SheetProtectionValue.selectLockedCells) == false) {
				if (actualLockingValues.ContainsKey(Worksheet.SheetProtectionValue.selectLockedCells) == false) {
					actualLockingValues.Add(Worksheet.SheetProtectionValue.selectLockedCells, 1);
				}
			}
			if (sheet.SheetProtectionValues.Contains(Worksheet.SheetProtectionValue.selectUnlockedCells) == false 
					|| sheet.SheetProtectionValues.Contains(Worksheet.SheetProtectionValue.selectLockedCells) == false) {
				if (actualLockingValues.ContainsKey(Worksheet.SheetProtectionValue.selectUnlockedCells) == false) {
					actualLockingValues.Add(Worksheet.SheetProtectionValue.selectUnlockedCells, 1);
				}
			}
			if (sheet.SheetProtectionValues.Contains(Worksheet.SheetProtectionValue.formatCells))
				actualLockingValues.Add(Worksheet.SheetProtectionValue.formatCells, 0);
			if (sheet.SheetProtectionValues.Contains(Worksheet.SheetProtectionValue.formatColumns))
				actualLockingValues.Add(Worksheet.SheetProtectionValue.formatColumns, 0);
			if (sheet.SheetProtectionValues.Contains(Worksheet.SheetProtectionValue.formatRows))
				actualLockingValues.Add(Worksheet.SheetProtectionValue.formatRows, 0);
			if (sheet.SheetProtectionValues.Contains(Worksheet.SheetProtectionValue.insertColumns))
				actualLockingValues.Add(Worksheet.SheetProtectionValue.insertColumns, 0);
			if (sheet.SheetProtectionValues.Contains(Worksheet.SheetProtectionValue.insertRows))
				actualLockingValues.Add(Worksheet.SheetProtectionValue.insertRows, 0);
			if (sheet.SheetProtectionValues.Contains(Worksheet.SheetProtectionValue.insertHyperlinks))
				actualLockingValues.Add(Worksheet.SheetProtectionValue.insertHyperlinks, 0);
			if (sheet.SheetProtectionValues.Contains(Worksheet.SheetProtectionValue.deleteColumns))
				actualLockingValues.Add(Worksheet.SheetProtectionValue.deleteColumns, 0);
			if (sheet.SheetProtectionValues.Contains(Worksheet.SheetProtectionValue.deleteRows))
				actualLockingValues.Add(Worksheet.SheetProtectionValue.deleteRows, 0);
			if (sheet.SheetProtectionValues.Contains(Worksheet.SheetProtectionValue.sort))
				actualLockingValues.Add(Worksheet.SheetProtectionValue.sort, 0);
			if (sheet.SheetProtectionValues.Contains(Worksheet.SheetProtectionValue.autoFilter))
				actualLockingValues.Add(Worksheet.SheetProtectionValue.autoFilter, 0);
			if (sheet.SheetProtectionValues.Contains(Worksheet.SheetProtectionValue.pivotTables))
				actualLockingValues.Add(Worksheet.SheetProtectionValue.pivotTables, 0);

			StringBuilder sb = new StringBuilder();
			sb.Append("<sheetProtection");
			string temp;
			foreach (KeyValuePair<Worksheet.SheetProtectionValue, int> item in actualLockingValues) {
				try {
					temp = Enum.GetName(typeof(Worksheet.SheetProtectionValue), item.Key); // Note! If the enum names differs from the OOXML definitions, this method will cause invalid OOXML entries
					sb.Append(" ").Append(temp).Append("=\"")
						.Append(item.Value.ToString("G", culture)).Append("\"");
				}
				catch { }
			}
			if (string.IsNullOrEmpty(sheet.SheetProtectionPassword) == false) {
				string hash = GeneratePasswordHash(sheet.SheetProtectionPassword);
				sb.Append(" password=\"").Append(hash).Append("\"");
			}
			sb.Append(" sheet=\"1\"/>");
			return sb.ToString();
		}


		/// <summary>
		/// 创建应用程序字符串
		/// </summary>
		private string CreateAppString() {
			if (workbook.WorkbookMetadata == null) return string.Empty;
			Metadata md = workbook.WorkbookMetadata;
			StringBuilder sb = new StringBuilder();
			AppendXMLtag(sb, "0", "TotalTime", null);
			AppendXMLtag(sb, md.Application, "Application", null);
			AppendXMLtag(sb, "0", "DocSecurity", null);
			AppendXMLtag(sb, "false", "ScaleCrop", null);
			AppendXMLtag(sb, md.Manager, "Manager", null);
			AppendXMLtag(sb, md.Company, "Company", null);
			AppendXMLtag(sb, "false", "LinksUpToDate", null);
			AppendXMLtag(sb, "false", "SharedDoc", null);
			AppendXMLtag(sb, md.HyperlinkBase, "HyperlinkBase", null);
			AppendXMLtag(sb, "false", "HyperlinksChanged", null);
			AppendXMLtag(sb, md.ApplicationVersion, "AppVersion", null);
			return sb.ToString();
		}

		private string CreateCorePropertiesString() {
			if (workbook.WorkbookMetadata == null) return string.Empty;
			Metadata md = workbook.WorkbookMetadata;
			StringBuilder sb = new StringBuilder();
			AppendXMLtag(sb, md.Title, "title", "dc");
			AppendXMLtag(sb, md.Subject, "subject", "dc");
			AppendXMLtag(sb, md.Creator, "creator", "dc");
			AppendXMLtag(sb, md.Creator, "lastModifiedBy", "cp");
			AppendXMLtag(sb, md.Keywords, "keywords", "cp");
			AppendXMLtag(sb, md.Description, "description", "dc");

			string time = DateTime.Now.ToString("yyyy-MM-ddThh:mm:ssZ");
			sb.Append("<dcterms:created xsi:type=\"dcterms:W3CDTF\">" + time + "</dcterms:created>");
			sb.Append("<dcterms:modified xsi:type=\"dcterms:W3CDTF\">" + time + "</dcterms:modified>");

			AppendXMLtag(sb, md.Category, "category", "cp");
			AppendXMLtag(sb, md.ContentStatus, "contentStatus", "cp");

			return sb.ToString();
		}

		private string CreateStyleFontString(List<Style.Font> fontStyles) {
			StringBuilder sb = new StringBuilder();
			foreach (Style.Font item in fontStyles) {
				sb.Append("<font>");
				if (item.Bold == true) sb.Append("<b/>");
				if (item.Italic == true) sb.Append("<i/>");
				if (item.Underline == true) sb.Append("<u/>");
				if (item.DoubleUnderline == true) sb.Append("<u val=\"double\"/>");
				if (item.Strike == true) sb.Append("<strike/>");
				if (item.VerticalAlign == Style.Font.VerticalAlignValue.subscript) 
					sb.Append("<vertAlign val=\"subscript\"/>");
				else if (item.VerticalAlign == Style.Font.VerticalAlignValue.superscript) 
					sb.Append("<vertAlign val=\"superscript\"/>");
				sb.Append("<sz val=\"").Append(item.Size.ToString("G", culture)).Append("\"/>");
				if (string.IsNullOrEmpty(item.ColorValue))
					sb.Append("<color indexed=\"8\"/>");
				else
					sb.Append("<color rgb=\"").Append(item.ColorValue).Append("\"/>");
				sb.Append("<name val=\"").Append(item.Name).Append("\"/>");
				sb.Append("<charset val=\"134\"/>");
				if (string.IsNullOrEmpty(item.Charset) == false)
					sb.Append("<charset val=\"").Append(item.Charset).Append("\"/>");
				sb.Append("</font>");
			}
			return sb.ToString();
		}

		private string CreateStyleBorderString(List<Style.Border> borderStyles) {
			StringBuilder sb = new StringBuilder();
			foreach (Style.Border item in borderStyles) {
				if (item.DiagonalDown == true && item.DiagonalUp == false) 
					sb.Append("<border diagonalDown=\"1\">");
				else if (item.DiagonalDown == false && item.DiagonalUp == true) 
					sb.Append("<border diagonalUp=\"1\">");
				else if (item.DiagonalDown == true && item.DiagonalUp == true) 
					sb.Append("<border diagonalDown=\"1\" diagonalUp=\"1\">");
				else sb.Append("<border>");

				if (item.LeftStyle != Style.Border.StyleValue.none) {
					sb.Append("<left style=\"").Append(Style.Border.GetStyleName(item.LeftStyle)).Append("\">");
					if (string.IsNullOrEmpty(item.LeftColor) == true)
						sb.Append("<color rgb=\"").Append(item.LeftColor).Append("\"/>");
					else sb.Append("<color auto=\"1\"/>");
					sb.Append("</left>");
				}
				else sb.Append("<left/>");
				
				if (item.RightStyle != Style.Border.StyleValue.none) {
					sb.Append("<right style=\"").Append(Style.Border.GetStyleName(item.RightStyle)).Append("\">");
					if (string.IsNullOrEmpty(item.RightColor) == true)
						sb.Append("<color rgb=\"").Append(item.RightColor).Append("\"/>");
					else sb.Append("<color auto=\"1\"/>");
					sb.Append("</right>");
				}
				else sb.Append("<right/>");
	
				if (item.TopStyle != Style.Border.StyleValue.none) {
					sb.Append("<top style=\"").Append(Style.Border.GetStyleName(item.TopStyle)).Append("\">");
					if (string.IsNullOrEmpty(item.TopColor) == true) 
						sb.Append("<color rgb=\"").Append(item.TopColor).Append("\"/>");
					else sb.Append("<color auto=\"1\"/>");
					sb.Append("</top>");
				}
				else sb.Append("<top/>");

				if (item.BottomStyle != Style.Border.StyleValue.none) {
					sb.Append("<bottom style=\"").Append(Style.Border.GetStyleName(item.BottomStyle)).Append("\">");
					if (string.IsNullOrEmpty(item.BottomColor) == true)
						sb.Append("<color rgb=\"").Append(item.BottomColor).Append("\"/>");
					else sb.Append("<color auto=\"1\"/>");
					sb.Append("</bottom>");
				}
				else sb.Append("<bottom/>");

				if (item.DiagonalStyle != Style.Border.StyleValue.none) {
					sb.Append("<diagonal style=\"").Append(Style.Border.GetStyleName(item.DiagonalStyle)).Append("\">");
					if (string.IsNullOrEmpty(item.DiagonalColor) == true) 
						sb.Append("<color rgb=\"").Append(item.DiagonalColor).Append("\"/>");
					else sb.Append("<color auto=\"1\"/>");
					sb.Append("</diagonal>");
				}
				else sb.Append("<diagonal/>");

				sb.Append("</border>");
			}
			return sb.ToString();
		}

		/// <summary>
		/// 创建自定义的调色板，主要是替换了其中两种不常用颜色作为表头背景色与记录交替行背景色
		/// </summary>
		/// <returns></returns>
		private string CreateColorsString() {
			return "<colors><indexedColors>"
				+ "<rgbColor rgb=\"00000000\"/>" + "<rgbColor rgb=\"00FFFFFF\"/>"
				+ "<rgbColor rgb=\"00FF0000\"/>" + "<rgbColor rgb=\"0000FF00\"/>"
				+ "<rgbColor rgb=\"000000FF\"/>" + "<rgbColor rgb=\"00FFFF00\"/>"
				+ "<rgbColor rgb=\"00FF00FF\"/>" + "<rgbColor rgb=\"0000FFFF\"/>"
				+ "<rgbColor rgb=\"00000000\"/>" + "<rgbColor rgb=\"00FFFFFF\"/>"
				+ "<rgbColor rgb=\"00FF0000\"/>" + "<rgbColor rgb=\"0000FF00\"/>"
				+ "<rgbColor rgb=\"000000FF\"/>" + "<rgbColor rgb=\"00FFFF00\"/>"
				+ "<rgbColor rgb=\"00FF00FF\"/>" + "<rgbColor rgb=\"0000FFFF\"/>"
				+ "<rgbColor rgb=\"00800000\"/>" + "<rgbColor rgb=\"00008000\"/>"
				+ "<rgbColor rgb=\"00000080\"/>" + "<rgbColor rgb=\"00808000\"/>"
				+ "<rgbColor rgb=\"00800080\"/>" + "<rgbColor rgb=\"00008080\"/>"
				+ "<rgbColor rgb=\"00C0C0C0\"/>" + "<rgbColor rgb=\"00808080\"/>"
				+ "<rgbColor rgb=\"009999FF\"/>" + "<rgbColor rgb=\"00993366\"/>"
				+ "<rgbColor rgb=\"00FFFFCC\"/>" + "<rgbColor rgb=\"00CCFFFF\"/>"
				+ "<rgbColor rgb=\"00660066\"/>" + "<rgbColor rgb=\"00FF8080\"/>"
				+ "<rgbColor rgb=\"000066CC\"/>" + "<rgbColor rgb=\"00CCCCFF\"/>"
				+ "<rgbColor rgb=\"00000080\"/>" + "<rgbColor rgb=\"00FF00FF\"/>"
				+ "<rgbColor rgb=\"00FFFF00\"/>" + "<rgbColor rgb=\"0000FFFF\"/>"
				+ "<rgbColor rgb=\"00800080\"/>" + "<rgbColor rgb=\"00800000\"/>"
				+ "<rgbColor rgb=\"00008080\"/>" + "<rgbColor rgb=\"000000FF\"/>"
				+ "<rgbColor rgb=\"0000CCFF\"/>" + "<rgbColor rgb=\"00CCFFFF\"/>"
				+ "<rgbColor rgb=\"00CCFFCC\"/>" + "<rgbColor rgb=\"00FFFF99\"/>"
				+ "<rgbColor rgb=\"0099CCFF\"/>" + "<rgbColor rgb=\"00DCDCDC\"/>"
				+ "<rgbColor rgb=\"00CC99FF\"/>" + "<rgbColor rgb=\"00F2F2F2\"/>"
				+ "<rgbColor rgb=\"003366FF\"/>" + "<rgbColor rgb=\"0033CCCC\"/>"
				+ "<rgbColor rgb=\"0099CC00\"/>" + "<rgbColor rgb=\"00FFCC00\"/>"
				+ "<rgbColor rgb=\"00FF9900\"/>" + "<rgbColor rgb=\"00FF6600\"/>"
				+ "<rgbColor rgb=\"00666699\"/>" + "<rgbColor rgb=\"00969696\"/>"
				+ "<rgbColor rgb=\"00003366\"/>" + "<rgbColor rgb=\"00339966\"/>"
				+ "<rgbColor rgb=\"00003300\"/>" + "<rgbColor rgb=\"00333300\"/>"
				+ "<rgbColor rgb=\"00993300\"/>" + "<rgbColor rgb=\"00993366\"/>"
				+ "<rgbColor rgb=\"00333399\"/>" + "<rgbColor rgb=\"00333333\"/>"
				+ "</indexedColors></colors>";
		}

		private string CreateStyleFillString(List<Style.Fill> fillStyles) {
			StringBuilder sb = new StringBuilder();
			foreach (Style.Fill item in fillStyles) {
				sb.Append("<fill>");
				sb.Append("<patternFill patternType=\"").Append(Style.Fill.GetPatternName(item.PatternFill)).Append("\"");
				if (item.PatternFill == Style.Fill.PatternValue.solid) {
					sb.Append(">");
					sb.Append("<fgColor rgb=\"").Append(item.ForegroundColor).Append("\"/>");
					sb.Append("<bgColor indexed=\"").Append(item.IndexedColor.ToString("G", culture)).Append("\"/>");
					sb.Append("</patternFill>");
				}
				else if (item.PatternFill == Style.Fill.PatternValue.mediumGray 
						|| item.PatternFill == Style.Fill.PatternValue.lightGray
						|| item.PatternFill == Style.Fill.PatternValue.gray0625 
						|| item.PatternFill == Style.Fill.PatternValue.darkGray) {
					sb.Append(">");
					sb.Append("<fgColor rgb=\"").Append(item.ForegroundColor).Append("\"/>");
					if (string.IsNullOrEmpty(item.BackgroundColor) == false)
						sb.Append("<bgColor rgb=\"").Append(item.BackgroundColor).Append("\"/>");
					sb.Append("</patternFill>");
				}
				else sb.Append("/>");
				sb.Append("</fill>");
			}
			return sb.ToString();
		}

		private string CreateMruColorsString(List<Style.Font> fonts, List<Style.Fill> fills) {
			StringBuilder sb = new StringBuilder();
			List<string> tempColors = new List<string>();
			foreach (Style.Font item in fonts) {
				if (string.IsNullOrEmpty(item.ColorValue) == true) { continue; }
				if (item.ColorValue == Style.Fill.DEFAULTCOLOR) { continue; }
				if (tempColors.Contains(item.ColorValue) == false) { tempColors.Add(item.ColorValue); }
			}
			foreach (Style.Fill item in fills) {
				if (string.IsNullOrEmpty(item.BackgroundColor) == false) {
					if (item.BackgroundColor != Style.Fill.DEFAULTCOLOR) {
						if (tempColors.Contains(item.BackgroundColor) == false) { tempColors.Add(item.BackgroundColor); }
					}
				}
				if (string.IsNullOrEmpty(item.ForegroundColor) == false) {
					if (item.ForegroundColor != Style.Fill.DEFAULTCOLOR) {
						if (tempColors.Contains(item.ForegroundColor) == false) { tempColors.Add(item.ForegroundColor); }
					}
				}
			}
			if (tempColors.Count > 0) {
				sb.Append("<mruColors>");
				foreach (string item in tempColors)
					sb.Append("<color rgb=\"").Append(item).Append("\"/>");
				sb.Append("</mruColors>");
				return sb.ToString();
			}
			else {
				return string.Empty;
			}
		}

		/// <summary>
		/// 创建样式引用字符串
		/// </summary>
		private string CreateStyleXfsString(List<Style> styles) {
			StringBuilder sb = new StringBuilder();
			StringBuilder sb2 = new StringBuilder();
			string alignmentString, protectionString;
			int formatNumber, textRotation;
			foreach (Style item in styles) {
				textRotation = item.CurrentCellXf.CalculateInternalRotation();
				alignmentString = string.Empty;
				protectionString = string.Empty;
				if (item.CurrentCellXf.HorizontalAlign != Style.CellXf.HorizontalAlignValue.none
						|| item.CurrentCellXf.VerticalAlign != Style.CellXf.VerticallAlignValue.none
						|| item.CurrentCellXf.Alignment != Style.CellXf.TextBreakValue.none
						|| textRotation != 0) {
					sb2.Clear();
					sb2.Append("<alignment");
					if (item.CurrentCellXf.HorizontalAlign != Style.CellXf.HorizontalAlignValue.none) {
						sb2.Append(" horizontal=\"");
						if (item.CurrentCellXf.HorizontalAlign == Style.CellXf.HorizontalAlignValue.center)
							sb2.Append("center");
						else if (item.CurrentCellXf.HorizontalAlign == Style.CellXf.HorizontalAlignValue.right)
							sb2.Append("right");
						else if (item.CurrentCellXf.HorizontalAlign == Style.CellXf.HorizontalAlignValue.centerContinuous)
							sb2.Append("centerContinuous");
						else if (item.CurrentCellXf.HorizontalAlign == Style.CellXf.HorizontalAlignValue.distributed)
							sb2.Append("distributed");
						else if (item.CurrentCellXf.HorizontalAlign == Style.CellXf.HorizontalAlignValue.fill)
							sb2.Append("fill");
						else if (item.CurrentCellXf.HorizontalAlign == Style.CellXf.HorizontalAlignValue.general)
							sb2.Append("general");
						else if (item.CurrentCellXf.HorizontalAlign == Style.CellXf.HorizontalAlignValue.justify)
							sb2.Append("justify");
						else sb2.Append("left");
						sb2.Append("\"");
					}
					if (item.CurrentCellXf.VerticalAlign != Style.CellXf.VerticallAlignValue.none) {
						sb2.Append(" vertical=\"");
						if (item.CurrentCellXf.VerticalAlign == Style.CellXf.VerticallAlignValue.center)
							sb2.Append("center");
						else if (item.CurrentCellXf.VerticalAlign == Style.CellXf.VerticallAlignValue.distributed)
							sb2.Append("distributed");
						else if (item.CurrentCellXf.VerticalAlign == Style.CellXf.VerticallAlignValue.justify)
							sb2.Append("justify");
						else if (item.CurrentCellXf.VerticalAlign == Style.CellXf.VerticallAlignValue.top)
							sb2.Append("top");
						else sb2.Append("bottom");
						sb2.Append("\"");
					}

					if (item.CurrentCellXf.Alignment != Style.CellXf.TextBreakValue.none) {
						if (item.CurrentCellXf.Alignment == Style.CellXf.TextBreakValue.shrinkToFit)
							sb2.Append(" shrinkToFit=\"1");
						else sb2.Append(" wrapText=\"1");
						sb2.Append("\"");
					}
					if (textRotation != 0)
						sb2.Append(" textRotation=\"").Append(textRotation.ToString("G", culture)).Append("\"");
					sb2.Append("/>"); // </xf>
					alignmentString = sb2.ToString();
				}

				if (item.CurrentCellXf.Hidden == true || item.CurrentCellXf.Locked == true) {
					if (item.CurrentCellXf.Hidden == true && item.CurrentCellXf.Locked == true) {
						protectionString = "<protection locked=\"1\" hidden=\"1\"/>";
					}
					else if (item.CurrentCellXf.Hidden == true && item.CurrentCellXf.Locked == false) {
						protectionString = "<protection hidden=\"1\" locked=\"0\"/>";
					}
					else {
						protectionString = "<protection hidden=\"0\" locked=\"1\"/>";
					}
				}

				sb.Append("<xf numFmtId=\"");
				if (item.CurrentNumberFormat.IsCustomFormat == true) {
					sb.Append(item.CurrentNumberFormat.CustomFormatID.ToString("G", culture));
				}
				else {
					formatNumber = (int)item.CurrentNumberFormat.Number;
					sb.Append(formatNumber.ToString("G", culture));
				}
				sb.Append("\" borderId=\"").Append(item.CurrentBorder.InternalID.ToString("G", culture));
				sb.Append("\" fillId=\"").Append(item.CurrentFill.InternalID.ToString("G", culture));
				sb.Append("\" fontId=\"").Append(item.CurrentFont.InternalID.ToString("G", culture));
				if (item.CurrentFont.IsDefaultFont == false)
					sb.Append("\" applyFont=\"1");
				if (item.CurrentFill.PatternFill != Style.Fill.PatternValue.none)
					sb.Append("\" applyFill=\"1");
				if (item.CurrentBorder.IsEmpty() == false)
					sb.Append("\" applyBorder=\"1");
				if (alignmentString != string.Empty || item.CurrentCellXf.ForceApplyAlignment == true)
					sb.Append("\" applyAlignment=\"1");
				if (protectionString != string.Empty)
					sb.Append("\" applyProtection=\"1");
				if (item.CurrentNumberFormat.Number != Style.NumberFormat.FormatNumber.none)
					sb.Append("\" applyNumberFormat=\"1\"");
				else sb.Append("\"");

				if (alignmentString != string.Empty || protectionString != string.Empty) {
					sb.Append(">").Append(alignmentString)
						.Append(protectionString).Append("</xf>");
				}
				else sb.Append("/>");
			}
			return sb.ToString();
		}

		/// <summary>
		/// 创建列宽定义字符串
		/// </summary>
		private string CreateColsString(Worksheet worksheet) {
			if (worksheet.Columns.Count > 0) {
				string col;
				string hidden = "";
				StringBuilder sb = new StringBuilder();
				foreach (KeyValuePair<int, Worksheet.Column> column in worksheet.Columns) {
					if (column.Value.Width == worksheet.DefaultColumnWidth && column.Value.IsHidden == false) { continue; }
					if (worksheet.Columns.ContainsKey(column.Key)) {
						if (worksheet.Columns[column.Key].IsHidden == true) {
							hidden = " hidden=\"1\"";
						}
					}
					col = (column.Key + 1).ToString("G", culture); // Add 1 for Address
					sb.Append("<col customWidth=\"1\" width=\"")
						.Append(column.Value.Width.ToString("G", culture))
						.Append("\" max=\"").Append(col).Append("\" min=\"")
						.Append(col).Append("\"").Append(hidden).Append("/>");
				}
				string value = sb.ToString();
				if (value.Length > 0) return value;
				else return string.Empty;
			}
			else return string.Empty;
		}

		/// <summary>
		/// 创建格式化信息字符串
		/// </summary>
		private string CreateStyleNumberFormatString(List<Style.NumberFormat> numberFormatStyles, out int counter) {
			counter = 0;
			StringBuilder sb = new StringBuilder();
			foreach (Style.NumberFormat item in numberFormatStyles) {
				if (item.IsCustomFormat == true) {
					sb.Append("<numFmt formatCode=\"").Append(item.CustomFormatCode)
						.Append("\" numFmtId=\"").Append(item.CustomFormatID.ToString("G", culture))
						.Append("\"/>");
					counter++;
				}
			}
			return sb.ToString();
		}



		#region utilMethods

		public static string CreateUniqueName(int length) {
			byte[] rndByte = new byte[4];
			RNGcsp.GetBytes(rndByte);
			int res = BitConverter.ToInt32(rndByte, 0);
			int nds;
			Random rnd = new Random(res);
			StringBuilder sb = new StringBuilder();
			for (int i = 0; i < length; i++) {
				nds = rnd.Next(0, 2);
				if (nds == 0)
					sb.Append((char)rnd.Next(48, 57));
				else
					sb.Append((char)rnd.Next(65, 90));
			}
			return sb.ToString();
		}

		private bool AppendXMLtag(StringBuilder sb, string value, string tagName, string nameSpace) {
			if (string.IsNullOrEmpty(value)) return false;
			if (sb == null || string.IsNullOrEmpty(tagName)) return false;
			bool hasNoNs = string.IsNullOrEmpty(nameSpace);
			sb.Append('<');
			if (hasNoNs == false)
				sb.Append(nameSpace).Append(':');
			sb.Append(tagName).Append(">")
				.Append(EscapeXMLChars(value))
				.Append("</");
			if (hasNoNs == false) 
				sb.Append(nameSpace).Append(':');
			sb.Append(tagName).Append(">");
			return true;
		}

		private void WriteXMLStream(MemoryStream stream, XmlDocument document) {
			if (stream == null || !stream.CanWrite) return;
			document.Save(stream);
		}

		public static string EscapeXMLChars(string input) {
			if (string.IsNullOrEmpty(input)) return input;

			StringBuilder sb = new StringBuilder(input.Length + 256);
			char[] chs = { '&', '<', '>' };
			int idx = 0;
			int pos = input.IndexOfAny(chs, idx);
			while (pos != -1) {
				sb.Append(input, idx, pos - idx);
				char c = input[pos];
				switch (c) {
					case '&': sb.Append("&amp;"); break;
					case '<': sb.Append("&lt;"); break;
					case '>': sb.Append("&gt;"); break;
				}
				idx = pos + 1;
				pos = input.IndexOfAny(chs, idx);
			}
			sb.Append(input, idx, input.Length - idx);
			return sb.ToString();
		}

		public static string EscapeXMLAttributeChars(string input) {
			if (string.IsNullOrEmpty(input)) return input;

			StringBuilder sb = new StringBuilder(input.Length + 256);
			char ch = '"';
			int idx = 0;
			int pos = input.IndexOf(ch, idx);
			while (pos != -1) {
				sb.Append(input, idx, pos - idx).Append("&quot;");
				idx = pos + 1;
				pos = input.IndexOf(ch, idx);
			}
			sb.Append(input, idx, input.Length - idx);
			return sb.ToString();
		}

		public static void CopyStream(System.IO.MemoryStream sourceStream, System.IO.Stream targetStream) {
			if (sourceStream == null || targetStream == null) {
				throw new IOException("The source or target memory stream to create a workbook part was not defined.");
			}
			try {
				byte[] buffer = sourceStream.GetBuffer();
				targetStream.Write(buffer, 0, (int)sourceStream.Length);
			}
			catch (Exception e) {
				throw new IOException("The memory stream to create a workbook part could not be copied.", e);
			}
		}

		/// <summary>
		/// 转化C#时间为Excel时间格式（OLE格式）
		/// </summary>
		public static string GetOADateTimeString(DateTime date) {
			try {
				return date.ToOADate().ToString();
			}
			catch (Exception e) {
				throw new FormatException("The date could not be transformed into Excel format (OADate).", e);
			}
		}

		/// <summary>
		/// 创建工作薄口令保护字符串
		/// </summary>
		public static string GeneratePasswordHash(string password) {
			if (string.IsNullOrEmpty(password)) { return string.Empty; }
			int PasswordLength = password.Length;
			int passwordHash = 0;
			char character;
			for (int i = PasswordLength; i > 0; i--) {
				character = password[i - 1];
				passwordHash = ((passwordHash >> 14) & 0x01) | ((passwordHash << 1) & 0x7fff);
				passwordHash ^= character;
			}
			passwordHash = ((passwordHash >> 14) & 0x01) | ((passwordHash << 1) & 0x7fff);
			passwordHash ^= (0x8000 | ('N' << 8) | 'K');
			passwordHash ^= PasswordLength;
			return passwordHash.ToString("X");
		}

		#endregion

		public class SortedMap : IEnumerable<SortedMap.Tuple> {
			private List<Tuple> _entries;

			public int Count {
				get { return _entries.Count; }
			}

			public string this[string key] {
				get {
					int s = _entries.Count;
					for (int i = 0; i < s; i++) {
						if (_entries[i].Key == key) {
							return _entries[i].Value;
						}
					}
					return null;
				}
			}

			public SortedMap() {
				_entries = new List<Tuple>();
			}


			public int Add(string key, string value) {
				int s = _entries.Count;
				for (int i = 0; i < s; i++) {
					if (_entries[i].Key == key) {
						_entries[i] = new Tuple(key, value);
						return i;
					}
				}
				_entries.Add(new Tuple(key, value));
				return s;
			}

			public bool ContainsKey(string key) {
				int s = _entries.Count;
				for (int i = 0; i < s; i++) {
					if (_entries[i].Key == key) {
						return true;
					}
				}
				return false;
			}

			public void Clear() {
				_entries.Clear();
			}

			public List<string> Keys() {
				List<string> output = new List<string>();
				int s = _entries.Count;
				for (int i = 0; i < s; i++) {
					output.Add(_entries[i].Key);
				}
				return output;
			}

			public List<string> Values() {
				List<string> output = new List<string>();
				int s = _entries.Count;
				for (int i = 0; i < s; i++) {
					output.Add(_entries[i].Value);
				}
				return output;
			}

			public class Tuple {
				public string Key { get; set; }
				public string Value { get; set; }
				public Tuple(string key, string value) {
					Key = key;
					Value = value;
				}
			}

			#region IEnumerable implementation
			public IEnumerator<SortedMap.Tuple> GetEnumerator() {
				return _entries.GetEnumerator();
			}
			#endregion
			#region IEnumerable implementation
			IEnumerator IEnumerable.GetEnumerator() {
				return GetEnumerator();
			}
			#endregion
		}

		public class DocumentPath {
			public string Filename { get; set; }
			public string Path { get; set; }

			public DocumentPath() {
			}

			public DocumentPath(string filename, string path) {
				Filename = filename;
				Path = path;
			}

			public string GetFullPath() {
				if (Path == null) { return Filename; }
				if (Path == "") { return Filename; }
				if (Path[Path.Length - 1] == System.IO.Path.AltDirectorySeparatorChar 
						|| Path[Path.Length - 1] == System.IO.Path.DirectorySeparatorChar) {
					return System.IO.Path.AltDirectorySeparatorChar.ToString() + Path + Filename;
				}
				else {
					return System.IO.Path.AltDirectorySeparatorChar.ToString() + Path + System.IO.Path.AltDirectorySeparatorChar.ToString() + Filename;
				}
			}

		}

	}
}
